import os
import sqlite3
import uuid
from datetime import datetime


def init_db():
    if not os.path.exists('data'):
        os.makedirs('data')

    conn = sqlite3.connect('data/questions.db')
    cursor = conn.cursor()

    # 创建题目表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS questions (
        id TEXT PRIMARY KEY,
        question_type TEXT NOT NULL,
        content TEXT NOT NULL,
        content_image TEXT,
        difficulty INTEGER,
        tags TEXT,
        category TEXT,
        answer TEXT NOT NULL,
        answer_image TEXT,
        notes TEXT,
        is_favorite INTEGER DEFAULT 0,
        md5 TEXT UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    ''')

    # 创建备份记录表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS backups (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        filename TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        notes TEXT
    )
    ''')

    # 新增用户配置表
    cursor.execute('''CREATE TABLE IF NOT EXISTS user_settings
                 (key TEXT PRIMARY KEY,
                  value TEXT)''')

    # 插入默认字体大小配置
    cursor.execute('''INSERT OR IGNORE INTO user_settings (key, value) 
                 VALUES ('font_size', '20')''')

    # 创建试卷表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS exams (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,  
        total_score INTEGER,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )''')

    # 创建试卷与试题关联表，并新增 question_order 字段
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS exam_questions (
        exam_id TEXT,
        question_id TEXT,
        score INTEGER,
        question_order INTEGER,  -- 新增字段：试题顺序
        FOREIGN KEY(exam_id) REFERENCES exams(id),
        FOREIGN KEY(question_id) REFERENCES questions(id)
    )
    ''')
    cursor.execute('''CREATE TABLE IF NOT EXISTS settings
                      (id INTEGER PRIMARY KEY, api_key TEXT)''')

    conn.commit()
    conn.close()


# 新增操作函数
def create_exam(title, questions_with_scores):
    conn = get_db_connection()
    exam_id = str(uuid.uuid4())
    try:
        # 确保插入语句包含title字段
        conn.execute('INSERT INTO exams (id, title, total_score) VALUES (?, ?, ?)',
                     (exam_id, title, sum(q['score'] for q in questions_with_scores)))

        # 新增：为每个题目分配 question_order
        for order, q in enumerate(questions_with_scores, start=1):
            conn.execute('INSERT INTO exam_questions (exam_id, question_id, score, question_order) VALUES (?, ?, ?, ?)',
                         (exam_id, q['id'], q['score'], order))
        conn.commit()
        return exam_id
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()


def search_questions(criteria=None):
    """支持无条件的全量查询"""
    conn = get_db_connection()
    try:
        query = "SELECT * FROM questions"
        params = []

        if criteria:
            conditions = []
            if criteria.get('type'):
                conditions.append("question_type = ?")
                params.append(criteria['type'])
            if criteria.get('difficulty'):
                conditions.append("difficulty = ?")
                params.append(criteria['difficulty'])
            if criteria.get('tags'):
                conditions.append("tags LIKE ?")
                params.append(f"%{criteria['tags']}%")

            if conditions:
                query += " WHERE " + " AND ".join(conditions)

        cursor = conn.execute(query, params)
        return [dict(row) for row in cursor.fetchall()]
    finally:
        conn.close()

def get_exam(exam_id):
    conn = get_db_connection()
    exam = conn.execute('SELECT * FROM exams WHERE id = ?', (exam_id,)).fetchone()
    questions = conn.execute('''SELECT q.*, eq.score
                              FROM exam_questions eq
                              JOIN questions q ON eq.question_id = q.id
                              WHERE eq.exam_id = ?''', (exam_id,)).fetchall()
    conn.close()
    return {'exam': dict(exam), 'questions': [dict(q) for q in questions]}

def get_db_connection():
    conn = sqlite3.connect('data/questions.db')
    conn.row_factory = sqlite3.Row  # 关键设置
    return conn


def backup_database():
    if not os.path.exists('backups'):
        os.makedirs('backups')

    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    backup_file = f'backups/backup_{timestamp}.db'

    conn = get_db_connection()
    conn.backup(sqlite3.connect(backup_file))
    conn.close()

    # 记录备份
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('INSERT INTO backups (filename, notes) VALUES (?, ?)',
                   (backup_file, f'Manual backup at {timestamp}'))
    conn.commit()
    conn.close()

    return backup_file


def restore_database(backup_file):
    if not os.path.exists(backup_file):
        return False

    # 先备份当前数据库
    current_backup = backup_database()

    try:
        backup_conn = sqlite3.connect(backup_file)
        main_conn = sqlite3.connect('data/questions.db')

        # 清空当前数据库
        main_conn.execute('DROP TABLE IF EXISTS questions')
        main_conn.execute('DROP TABLE IF EXISTS backups')
        main_conn.commit()

        # 恢复备份
        backup_conn.backup(main_conn)

        return True
    except Exception as e:
        print(f"Restore failed: {e}")
        # 尝试恢复原来的数据库
        try:
            sqlite3.connect(current_backup).backup(main_conn)
        except:
            pass
        return False
    finally:
        backup_conn.close()
        main_conn.close()
# database.py
import sqlite3
from datetime import datetime


def get_exams(page=1, page_size=15):
    """分页获取试卷列表"""
    offset = (page - 1) * page_size
    conn = get_db_connection()
    try:
        cursor = conn.execute('''
            SELECT e.id, e.title, e.total_score, e.created_at,
                   COUNT(eq.question_id) AS question_count
            FROM exams e
            LEFT JOIN exam_questions eq ON e.id = eq.exam_id
            GROUP BY e.id
            ORDER BY e.created_at DESC
            LIMIT ? OFFSET ?
        ''', (page_size, offset))
        return [dict(row) for row in cursor.fetchall()]
    finally:
        conn.close()


def delete_exam(exam_id):
    """安全删除试卷（包含事务处理）"""
    conn = get_db_connection()
    try:
        conn.execute('BEGIN TRANSACTION')
        # 先删除关联题目
        conn.execute('DELETE FROM exam_questions WHERE exam_id = ?', (exam_id,))
        # 再删除试卷
        conn.execute('DELETE FROM exams WHERE id = ?', (exam_id,))
        conn.commit()
        return True
    except Exception as e:
        conn.rollback()
        print(f"删除失败: {str(e)}")
        return False
    finally:
        conn.close()


def search_exams(keyword):
    """根据关键词搜索试卷（支持模糊搜索）"""
    conn = get_db_connection()
    try:
        query = '''
            SELECT e.id, 
                   e.title,
                   e.total_score,
                   COUNT(eq.question_id) AS question_count,
                   e.created_at
            FROM exams e
            LEFT JOIN exam_questions eq ON e.id = eq.exam_id
            WHERE e.title LIKE ?
            GROUP BY e.id
            ORDER BY e.created_at DESC
        '''
        print(f"Executing SQL: {query}")
        print(f"With parameters: ('%{keyword}%')")
        cursor = conn.execute(query, (f'%{keyword}%',))
        return [dict(row) for row in cursor.fetchall()]
    finally:
        conn.close()


def update_exam_name(exam_id, new_name):
    """更新试卷名称（带输入验证）"""
    if not new_name.strip():
        raise ValueError("试卷名称不能为空")

    conn = get_db_connection()
    try:
        conn.execute(
            'UPDATE exams SET title = ? WHERE id = ?',
            (new_name.strip(), exam_id)
        )
        conn.commit()
        return True
    except Exception as e:
        print(f"更新失败: {str(e)}")
        return False
    finally:
        conn.close()

